using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data.SqlClient;
using System.Drawing;
using System.Data;
using System.IO;
using System.Text;
using System.Windows.Forms;
using CoreLab.Oracle;
using Common;
using Common.Utilities;

namespace WindowsUserControl.Database
{
    /// <summary>
    /// 
    /// </summary>
    public partial class BFileBrowser : UserControl
    {
        /// <summary>
        /// 
        /// </summary>
        public BFileBrowser()
        {
            InitializeComponent();
        }

        private void btn_GetData_Click(object sender, EventArgs e)
        {
            if(!string.IsNullOrEmpty(this.txt_DataSource.Text))
            {
                DataProviderType providerType = DataProviderType.MSSQL;
                string connStr = "Data Source=" + this.txt_DataSource.Text + ";";
                if(string.IsNullOrEmpty(this.txt_Catalog.Text))
                {
                    providerType = DataProviderType.Oracle;
                }
                else
                {
                    connStr += "Database=" + this.txt_Catalog.Text + ";";
                    providerType = DataProviderType.MSSQL;
                }
                if((!string.IsNullOrEmpty(this.txt_UserName.Text))&&(!string.IsNullOrEmpty(this.txt_Password.Text)))
                {
                    connStr += "User ID=" + this.txt_UserName.Text + ";Password=" + this.txt_Password.Text;
                }
                else
                {
                    connStr += "Integrated Security=SSPI";
                }
                TableColumn[] cols = DbUtil.GetTableColumns(providerType, connStr, this.txt_SQL.Text);
                DataTable dtPreview = new DataTable();
                foreach(TableColumn col in cols)
                {
                    dtPreview.Columns.Add(col.ColumnName, FieldDataType.ToSystemType(col.DbType));
                }

                try
                {
                    IDbConnection conn;
                    if(providerType==DataProviderType.MSSQL)
                    {
                        conn = new SqlConnection(connStr);
                    }
                    else
                    {
                        conn = new OracleConnection(connStr);
                    }
                    try
                    {
                        
                        conn.Open();
                        string selectSql = this.txt_SQL.Text;
                        IDbCommand cmd;
                        IDataReader reader;
                        if (providerType == DataProviderType.MSSQL)
                        {
                            cmd = ((SqlConnection) conn).CreateCommand();
                            cmd.CommandText = selectSql;
                            reader = ((SqlCommand) cmd).ExecuteReader();
                            // daSelect = new SqlDataAdapter(selectSql, (SqlConnection) conn);
                        }
                        else
                        {
                            cmd = ((OracleConnection) conn).CreateCommand();
                            cmd.CommandText = selectSql;
                            reader = ((OracleCommand) cmd).ExecuteReader();
                            //daSelect = new OracleDataAdapter(selectSql, (OracleConnection) conn);
                        }
                        int current = 0;
                        int start = 1;
                        int finish = 50;
                        while(reader.Read())
                        {
                            current++;
                            if(current>=start)
                            {
                                DataRow drData = dtPreview.NewRow();
                                foreach(TableColumn col in cols)
                                {
                                    drData[col.ColumnName] = reader[col.ColumnName];
                                }
                                dtPreview.Rows.Add(drData);
                            }
                            if(current >finish)
                            {
                                break;
                            }
                        }
                        reader.Close();
                    }
                    catch (Exception ex1)
                    {
                        MessageBox.Show(ex1.Message, ex1.Source);
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, ex.Source);
                }
            }
        }

        private void PreviewData(TableColumn[] cols, DataTable dtPreview)
        {
            this.dv_Data.Rows.Clear();
            this.dv_Data.Columns.Clear();
            System.Collections.Hashtable htImgCols = new System.Collections.Hashtable();
            for (int i = 0; i < cols.Length; i++)
            {
                switch (cols[i].DbType)
                {
                    case SqlDbType.Image:
                        DataGridViewImageColumn column = new DataGridViewImageColumn();
                        column.HeaderText = cols[i].ColumnName;
                        this.dv_Data.Columns.Add(column);
                        htImgCols.Add(cols[i].ColumnName, "");
                        break;
                    default:
                        DataGridViewTextBoxColumn column2 = new DataGridViewTextBoxColumn();
                        column2.HeaderText = cols[i].ColumnName;
                        this.dv_Data.Columns.Add(column2);
                        break;
                }
            }

            for (int i = 0; i < dtPreview.Rows.Count; i++)
            {
                DataGridViewRow drData = new DataGridViewRow();
                for (int k = 0; k < dtPreview.Columns.Count; k++)
                {
                    if (htImgCols.Contains(dtPreview.Columns[k].ColumnName))
                    {
                        DataGridViewImageCell cellImg = new DataGridViewImageCell();
                        cellImg.Value = Properties.Resources.adobe;
                        drData.Cells.Add(cellImg);
                    }
                    else
                    {
                        DataGridViewTextBoxCell cellTxt = new DataGridViewTextBoxCell();
                        cellTxt.Value = dtPreview.Rows[i][k].ToString();
                        drData.Cells.Add(cellTxt);
                    }
                }
                this.dv_Data.Rows.Add(drData);
            }
            // this.dv_Data.DataSource = dtPreview;
            this.dv_Data.Refresh();
            // this.dv_Data.Columns.Clear();
        }

        private void dv_Data_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            string programFilePath = string.Empty;
            string adobeProgFolderPath = @"C:\Program Files\Adobe";
            System.IO.DirectoryInfo dirInfo=new DirectoryInfo(adobeProgFolderPath);
            System.IO.FileInfo[] exeFileInfo = dirInfo.GetFiles("AcroRd32.exe", SearchOption.AllDirectories);
            if(exeFileInfo.Length==1)
            {
                programFilePath = exeFileInfo[0].FullName;
            }

            if (!string.IsNullOrEmpty(programFilePath))
            {
                if (e.RowIndex >= 0 && e.ColumnIndex >= 0)
                {
                    DataGridViewImageCell cell = this.dv_Data.Rows[e.RowIndex].Cells[e.ColumnIndex] as DataGridViewImageCell;
                    if (cell != null)
                    {
                        try
                        {
                            byte[] value = this.dv_Data.Rows[e.RowIndex].Cells[e.ColumnIndex].Value as byte[];
                            if (value != null)
                            {
                                this.PreviewBinaryFile(value, programFilePath);
                            }
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show(ex.Message, ex.Source);
                        }
                    }
                }
            }
        }

        private void PreviewBinaryFile(byte[] content, string programFilePath)
        {
            try
            {
                string appPath = System.Reflection.Assembly.GetExecutingAssembly().Location;
                string binFolderPath = System.IO.Path.GetDirectoryName(appPath);
                string tmpFilePath = System.IO.Path.Combine(binFolderPath, "Temp.pdf");
                if (System.IO.File.Exists(tmpFilePath))
                {
                    System.IO.FileInfo fileInfo = new System.IO.FileInfo(tmpFilePath);
                    fileInfo.Delete();
                }
                System.IO.BinaryWriter bWriter = new System.IO.BinaryWriter(new System.IO.StreamWriter(tmpFilePath, false, Encoding.Unicode).BaseStream, Encoding.Unicode);
                bWriter.Write(content);
                bWriter.Flush();
                bWriter.Close();

                System.Diagnostics.Process proc = new System.Diagnostics.Process();
                System.Diagnostics.ProcessStartInfo startInfo = new System.Diagnostics.ProcessStartInfo();
                startInfo.FileName = programFilePath;
                startInfo.Arguments = tmpFilePath;
                proc.StartInfo = startInfo;
                proc.Start();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, ex.Source);
            }
        }
    }
}
